package com.demo;

import com.demo.util.CamelUtils;
import com.demo.util.PropertiesUtils;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;

/**
 * @author gy
 * @date 2023/8/24
 */
public class DataBaseInfo35 {

    private static final String driver="com.mysql.cj.jdbc.Driver";

    private static final String ipAddrAndPort="192.168.6.35:23306";
//    private static final String ipAddrAndPort="127.0.0.1:3308";

    private static final String dbCatalog = "hlyy";

    private static final String username="sinoits";
//    private static final String username="root";

    private static final String password="sinoits@123";
//    private static final String password="123456";

    private static final String serverUrl ="jdbc:mysql://"+ipAddrAndPort+"?" +
            "useSSL=false&useUnicode=true&characterEncoding=utf8&autoReconnect=true" +
            "&rewriteBatchedStatements=TRUE&serverTimezone=Asia/Shanghai&allowMultiQueries=true" +
            "&useInformationSchema=true";//没有指定具体哪个数据库，现在获取的是整个连接
    private static final String url="jdbc:mysql://"+ipAddrAndPort+"/"+dbCatalog+"?" +
            "useSSL=false&useUnicode=true&characterEncoding=utf8&autoReconnect=true" +
            "&rewriteBatchedStatements=TRUE&serverTimezone=Asia/Shanghai&allowMultiQueries=true" +
            "&useInformationSchema=true";//指定hlyy数据库

//    private static final String tableName = "t_resource_monitor_alert_manage";
    private static final String tableName = "t_virtual_resource_manage";
//    private static final String tableName = "t_hardware_server_manage";
//    private static final String tableName = "t_sample_manage";
//    private static final String tableName = "t_evaluation_schedule";
//    private static final String tableName = "t_analysis_task_manage";
//    private static final String tableName = "t_information_database_property_manage";
    /**
     * 获取数据库基本信息
     * @throws Exception
     */
    @Test
    public void test01() throws Exception{
        //获取连接
        Class.forName(driver);//注册驱动
        Connection connection = DriverManager.getConnection(serverUrl, username,password);

        //获取元数据
        DatabaseMetaData metaData = connection.getMetaData();
        //获取数据库基本信息
        System.out.println(metaData.getUserName());
        System.out.println(metaData.supportsTransactions());//是否支持事务
        System.out.println(metaData.getDatabaseProductName());//数据库类型（MYSQL）
        connection.close();

        /**
         * 打印结果如下
         * root@localhost
         * true
         * MySQL
         */
    }

    /**
     * 获取数据库列表
     * @throws Exception
     */
    @Test
    public void test2() throws Exception{
        //获取连接
        Class.forName(driver);//注册驱动
        Connection connection = DriverManager.getConnection(serverUrl, username,password);

        //获取元数据
        DatabaseMetaData metaData = connection.getMetaData();
        //获取数据库列表名称
        ResultSet resultSet = metaData.getCatalogs();
        while (resultSet.next()){
            System.out.println(resultSet.getString(1));
        }
        resultSet.close();
        connection.close();
        /**
         * 打印连接中的所有数据库名称
         * activiti
         * dage
         * dk
         * dk1
         * dk_front
         * dk_front1
         * information_schema
         * laji
         * light_master
         * mysql
         * performance_schema
         * sakila
         * solr
         * sys
         * test
         * workflow
         * world
         */
    }

    /**
     * 获取指定数据库中的表信息
     * 打印出该数据库下的所有表名。
     * @throws Exception
     */
    @Test
    public void test3() throws Exception{

        //获取连接
        Class.forName(driver);//注册驱动
        Connection connection = DriverManager.getConnection(url, username,password);

        //获取元数据
        DatabaseMetaData metaData = connection.getMetaData();
        //获取数据库中表信息（mysql可以这样写，oracle会有一点区别）
        //参数1：当前操作的数据库 参数2：mysql可为空，oracle填写用户名（要大写） 参数3：null是查询所有表 非空是查询目标表 参数4：类型 table是表，view是视图
        ResultSet resultSet = metaData.getTables(dbCatalog, null, null, new String[]{"TABLE"});
        while (resultSet.next()){
            //会打印出该数据库下的所有表名
            String TABLE_CAT = resultSet.getString("TABLE_CAT");
            String TABLE_SCHEM = resultSet.getString("TABLE_SCHEM");
            String TABLE_NAME = resultSet.getString("TABLE_NAME");
            String TABLE_TYPE = resultSet.getString("TABLE_TYPE");
            String REMARKS = resultSet.getString("REMARKS");
            System.out.printf("表类别:%s, 表模式:%s, 表名称:%s, 表类型:%s, 表描述:%s\n",
                    TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,REMARKS);
        }
        resultSet.close();
        connection.close();
    }

    /**
     * 获取指定表中的字段信息
     * 打印出指定表的所有字段名。和  类型名字
     * @throws Exception
     */
    @Test
    public void test4() throws Exception{

        //获取连接
        Class.forName(driver);//注册驱动
        Connection connection = DriverManager.getConnection(url, username,password);

        //获取元数据
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet columns = metaData.getColumns(dbCatalog, null, tableName, null);
        while (columns.next()){
            String mysql_column_name = columns.getString("COLUMN_NAME");
            String mysql_column_remarks = columns.getString("REMARKS");
            String javaColumnName = CamelUtils.line2lowerCamel(mysql_column_name);
            String mysql_type_name = columns.getString("TYPE_NAME");
            String javaTypeName = PropertiesUtils.customMap.get(mysql_type_name);
            String mysql_column_size = columns.getString("COLUMN_SIZE");

            //会打印出指定表的所有字段名
            System.out.println(mysql_column_name + " "+javaColumnName +" "+mysql_type_name + " "+javaTypeName
                    + " "+mysql_column_size+ " "+mysql_column_remarks);

        }
    }

    /**
     * todo：只是用来测试
     * 获取指定表中的字段信息
     * 打印出指定表的所有字段名。和  类型名字
     * @throws Exception
     */
    @Test
    public void testWord() throws Exception{

        ArrayList<String> list = new ArrayList<>();
        list.add("num1");
        list.add("num2");
        list.add("text1");
        list.add("text2");
        list.add("text3");
        list.add("text4");

        //获取连接
        Class.forName(driver);//注册驱动
        Connection connection = DriverManager.getConnection(url, username,password);

        //获取元数据
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet columns = metaData.getColumns(dbCatalog, null, tableName, null);
        while (columns.next()){
            String mysql_column_name = columns.getString("COLUMN_NAME");
            String mysql_column_remarks = columns.getString("REMARKS");
            String javaColumnName = CamelUtils.line2lowerCamel(mysql_column_name);
            String mysql_type_name = columns.getString("TYPE_NAME");
            String javaTypeName = PropertiesUtils.customMap.get(mysql_type_name);

            if (list.contains(mysql_column_name)){
                continue;
            }
            System.out.println(mysql_column_name + "：" + mysql_column_remarks);
        }

        System.out.println("=====================");
        System.out.println("tableName: " + tableName);
    }

    /**
     * todo：只是用来测试
     * 获取指定表中的字段信息
     * 打印出指定表的所有字段名。和  类型名字
     * @throws Exception
     */
    @Test
    public void testLxsj() throws Exception{

        ArrayList<String> list = new ArrayList<>();
        list.add("num1");
        list.add("num2");
        list.add("text1");
        list.add("text2");
        list.add("text3");
        list.add("text4");

        //获取连接
        Class.forName(driver);//注册驱动
        Connection connection = DriverManager.getConnection(url, username,password);

        //获取元数据
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet columns = metaData.getColumns(dbCatalog, null, "t_route", null);
        while (columns.next()){
            String mysql_column_name = columns.getString("COLUMN_NAME");
            String mysql_column_remarks = columns.getString("REMARKS");
            String javaColumnName = CamelUtils.line2lowerCamel(mysql_column_name);
            String mysql_type_name = columns.getString("TYPE_NAME");
            String javaTypeName = PropertiesUtils.customMap.get(mysql_type_name);

            if (list.contains(mysql_column_name)){
                continue;
            }
            System.out.println("r."+mysql_column_name + " AS " + javaColumnName+",");
        }

        System.out.println("=====================");
        System.out.println("tableName: " + tableName);
    }



    /**
     * 测试参数元数据
     * 通过preparedStatement获取
     * 目的：获取sql参数中的属性信息
     */
    @Test
    public void test5() throws Exception{
        

        //获取连接
        Class.forName(driver);//注册驱动
        Connection connection = DriverManager.getConnection(url, username,password);

        String sql="select * from user where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,1);//设第一个参数为int 1
        //获取参数元数据
        ParameterMetaData metaData = preparedStatement.getParameterMetaData();
        //得到参数的个数
        int count = metaData.getParameterCount();//打印 1 （只有一个id参数）

        System.out.println(count);
        preparedStatement.close();
        connection.close();
    }


    /**
     * 测试结果集元数据
     * @throws Exception
     */
    @Test
    public void test6() throws Exception{

        //获取连接
        Class.forName(driver);//注册驱动
        Connection connection = DriverManager.getConnection(url, username,password);

        String sql="select * from user where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,1);//设第一个参数为int 1
        //查询
        ResultSet resultSet = preparedStatement.executeQuery();

        //获取结果集元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        //获取查询字段个数
        int count = metaData.getColumnCount();
        for(int i=1;i<=count;i++){
            //获取列名
            String columnName = metaData.getColumnName(i);//第i个列
            //获取字段类型  sql类型 varchar
            int columnType = metaData.getColumnType(i);
            //获取java类型  String
            String columnClassName = metaData.getColumnClassName(i);
            System.out.println(columnName+"---"+columnType+"---"+columnClassName);
        }

        preparedStatement.close();
        connection.close();

        /**
         * 打印
         * id---4---java.lang.Integer
         * account---12---java.lang.String
         * password---12---java.lang.String
         * islogin---4---java.lang.Integer
         */
    }
    
}
